package com.wiscamp.ninechapters.problems.infrastructure.mappers.sets;

import com.wiscamp.ninechapters.problems.infrastructure.po.sets.ProblemSetView;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.Collection;

/**
 * 习题集仓储对象
 *
 * @author yifeng
 */
@Repository
public interface ProblemSetViewMapper extends JpaRepository<ProblemSetView, Long>, JpaSpecificationExecutor<ProblemSetView> {
    /**
     * 通过代码获取习题集
     *
     * @param setCode
     * @param organizationId
     * @return
     */
    @Query(value = "select e from ProblemSetView e where e.organizationId = :organizationId and e.setCode = :setCode")
    ProblemSetView findBySetCode(String setCode, long organizationId);

    /**
     * 通过名称获取习题集列表
     *
     * @param setName
     * @param organizationId
     * @return
     */
    @Query(value = "select e from ProblemSetView e where e.organizationId = :organizationId and e.setName = :setName")
    Collection<ProblemSetView> findListBySetName(String setName, long organizationId);

    /**
     * 通过机构编号获取习题集列表
     *
     * @param organizationId
     * @return
     */
    @Query(value = "select e from ProblemSetView e where e.organizationId = :organizationId")
    Collection<ProblemSetView> findListByOrganization(long organizationId);

    /**
     * 分页获取习题集列表
     *
     * @param spec
     * @param pageable
     * @return
     */
    Page<ProblemSetView> findAll(Specification<ProblemSetView> spec, Pageable pageable);

    /**
     * 通过组题规则编号获取习题集列表
     *
     * @param ruleId
     * @return
     */
    @Query(value = "select e from ProblemSetView e where e.ruleId = :ruleId")
    Collection<ProblemSetView> findListByRule(long ruleId);

    /*@Query(nativeQuery = true, value = "select set_id, set_code, set_name, set_type, rule_id, organization_id, subject_id, total_problem_quantity, total_score, total_time_limit, is_true_problem, difficulty, remark, is_active, is_deleted, created_by, created_time, modified_by, modified_time, time_stamp, reference_id from problem_set e where e.created_by = :userId and e.time_stamp between :startDate and :endDate and e.is_deleted = 0")
    Collection<ProblemSetView> findListByDate(long userId, String startDate, String endDate);*/

    @Query(value = "select e from ProblemSetView e where e.creatorId = :creatorId and e.creationTime between :startDate and :endDate")
    Collection<ProblemSetView> findListByCreatorAndDate(long creatorId, LocalDateTime startDate, LocalDateTime endDate);

    @Query(value = "select distinct si.problemId from SetProblemPO si, ProblemSetView ps where ps.deleted = false and ps.setId = si.setId and ps.setCode like 'DC-%'")
    Collection<Long> findProblemIdsByChallengeHistory();
}